use supermarket;

INSERT OVERWRITE TABLE function1_1
SELECT  
    name, 
    SUM(CASE WHEN status = 'U' THEN 1 ELSE 0 END) AS up_status_value,  
    SUM(CASE WHEN status = 'D' THEN 1 ELSE 0 END) AS down_status_value  
FROM  dwd_shelves
GROUP BY  name; 

INSERT OVERWRITE TABLE category 
SELECT
    name,
    CASE name
        WHEN 'Chewing' THEN  'food'
        WHEN 'Chewing3' THEN  'food'
        WHEN 'Drinks' THEN  'food'
        WHEN 'Drinks0' THEN  'food'
        WHEN 'Notebook' THEN  'stationery'
        WHEN 'Notebook2' THEN  'stationery'
        WHEN 'Pencil' THEN  'stationery'
        WHEN 'Pencil1' THEN  'stationery'
        WHEN 'Toothpaste ' THEN  'household_products'
        WHEN 'Toothpaste 4' THEN  'household_products'
        END AS category
FROM  dwd_name;


INSERT OVERWRITE TABLE function1_2
SELECT 
    category,
    COUNT(category)
FROM category
GROUP BY category;

INSERT TABLE function3
SELECT
    name,
    SUM(CASE WHEN status = 'U' THEN 1 ELSE 0 END) AS up_status_value
    FROM
        dwd_shelves
    GROUP BY name
    ORDER BY up_status_value DESC LIMIT 3;

INSERT INTO recommend_category VALUES
('Chewing', 'Chewing3', 'similar_category', '1.0000'),
('Chewing3', 'Chewing', 'similar_category', '1.0000'),
('Drinks', 'Drinks0', 'similar_category', '1.0000'),
('Drinks0', 'Drinks', 'similar_category', '1.0000'),
('Notebook', 'Notebook2', 'similar_category', '1.0000'),
('Notebook2', 'Notebook', 'similar_category', '1.0000'),
('Pencil', 'Pencil1', 'similar_category', '1.0000'),
('Pencil1', 'Pencil', 'similar_category', '1.0000'),
('Toothpaste ', 'Toothpaste 4', 'similar_category', '1.0000'),
('Toothpaste 4', 'Toothpaste ', 'similar_category', '1.0000');

INSERT OVERWRITE TABLE recommend_CF  
SELECT  
    goods AS name,  
    SPLIT(recommend1, ':')[0] AS recommend,
    'CF' AS method,	
    SPLIT(recommend1, ':')[1] AS Similarity  
FROM data_CF  
UNION ALL  
SELECT  
    goods AS name,  
    SPLIT(recommend2, ':')[0] AS recommend,
    'CF' AS method,
    SPLIT(recommend2, ':')[1] AS Similarity 
FROM data_CF  
UNION ALL  
SELECT  
    goods AS name,  
    SPLIT(recommend2, ':')[0] AS recommend,
    'CF' AS method,	
    SPLIT(recommend2, ':')[1] AS Similarity 
FROM data_CF;

CREATE EXTERNAL TABLE recommend_sales
(
    name  STRING,
    recommend  STRING,
    method STRING,
    Similarity STRING
)
ROW FORMAT DELIMITED             
FIELDS TERMINATED BY '\t'
LOCATION '/home/flume/ads/recommend_sales' ;


INSERT OVERWRITE TABLE sales 
SELECT
    name,
    sum(sales) AS total_sales
FROM
    dwd_namesales
GROUP BY name
ORDER BY total_sales DESC;

INSERT OVERWRITE TABLE recommend_sales 
SELECT t1.name, t2.*
FROM(
    SELECT name FROM dwd_name GROUP BY name
) t1
CROSS JOIN(
    SELECT name, 'total_sales', 1.0000 - (ROW_NUMBER() OVER (ORDER BY total_sales DESC) - 1) * 0.2000  AS Similarity
    FROM  sales
    ORDER BY Similarity DESC
    LIMIT 3
) t2;

INSERT OVERWRITE function4
SELECT * FROM recommend_category
UNION ALL
SELECT * FROM recommend_sales
UNION ALL
SELECT * FROM recommend_CF;

INSERT OVERWRITE TABLE function4_part1
SELECT 
    name
FROM 
    dwd_name
GROUP BY
    name;

INSERT OVERWRITE TABLE function2
SELECT 
    t1.name,  
    split(t2.recommend, ',')[0] AS recoomend1,
    split(t2.recommend, ',')[1] AS recoomend2,
    split(t2.recommend, ',')[2] AS recoomend3
FROM
(   
    SELECT 
        name, 
        SUM(CASE WHEN status = 'U' THEN 1 ELSE 0 END) AS up_status_value
    FROM dwd_shelves
    GROUP BY name
    ORDER BY up_status_value LIMIT 3
) t1
CROSS JOIN(
    SELECT name, concat_ws(',', collect_list(recommend)) as recommend
    FROM recommend_CF
    GROUP BY name
) t2 
ON t2.name = t1.name;